package com.report;

import com.deepoove.poi.render.compute.ReadMapAccessor;
import org.jxls.jdbc.JdbcHelper;
import org.springframework.expression.spel.support.StandardEvaluationContext;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class Tools {
    public static String applySql(String sql, Map<String, String> params) {
        if (sql == null)
            return null;
        sql = sql.trim();
        if (sql.endsWith(";"))
            sql = sql.substring(0, sql.length() - 1);
        if (params == null || params.size() == 0)
            return sql;
        for (Map.Entry<String, String> entry : params.entrySet()) {
            String key = "${" + entry.getKey() + "}";
            sql = sql.replace(key, entry.getValue());
        }
        return sql;
    }

    public static List<Map<String, Object>> dbQuery(DataSource dataSource, String sql, Object... params)
            throws SQLException {
        List<Map<String, Object>> result;
        try (Connection con = dataSource.getConnection()) {
            result = new JdbcHelper(con).query(sql, params);
        }
        return result;
    }

    public static StandardEvaluationContext createSpelContext(Map<String, Object> context) {
        StandardEvaluationContext st = new StandardEvaluationContext(context);
        st.addPropertyAccessor(new ReadMapAccessor());
        st.setVariable("params", context.get("_params"));
        st.setVariable("time", new Date());
        return st;
    }
}
